package com.lin.easyexceldemo.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.github.pagehelper.PageHelper;
import com.lin.easyexceldemo.entity.UserTest;
import com.lin.easyexceldemo.mapper.UserTestMapper;
import com.lin.easyexceldemo.service.UserTestService;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.SneakyThrows;
import org.apache.ibatis.cursor.Cursor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;


@Service
public class UserTestServiceImpl implements UserTestService {
    /**
     * 一个sheet最多的条数
     */
    public static final int PER_SHEET_ROW_COUNT = 250000;
    /**
     * 每次写入的数量
     */
    public static final int PER_WRITE_ROW_COUNT = 50000;
    @Autowired
    private UserTestMapper userTestMapper;

    /**
     * 游标查询+分批次写出
     */
    @SneakyThrows
    @Override
    @Transactional
    public void exportByCursor(HttpServletResponse response) {
        String fileName = "test";
        long start = System.currentTimeMillis();
        ServletOutputStream outputStream = response.getOutputStream();
        WriteWorkbook writeWorkbook = new WriteWorkbook();
        writeWorkbook.setOutputStream(outputStream);
        writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
        ExcelWriter writer = EasyExcel.write(outputStream).build();

        Cursor<UserTest> cursor = userTestMapper.selectListByCursor();
        List<UserTest> writeList = new ArrayList<>();
        int i = 0;
        int j = 0;
        for (UserTest userTest : cursor) {
            writeList.add(userTest);
            j++;
            if (writeList.size() == PER_WRITE_ROW_COUNT || cursor.isConsumed()) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1))
                        .head(UserTest.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                writer.write(writeList, writeSheet);
                writeList.clear();
                if (j % PER_SHEET_ROW_COUNT == 0) {
                    i++;
                }
            }
        }
        System.out.println(j);

        // 下载EXCEL，返回给前段stream流
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        writer.finish();
        outputStream.flush();
        outputStream.close();
        System.out.println("游标查询导出结束：" + (System.currentTimeMillis() - start) + "ms");
    }

    /**
     * 普通分页查询+分批次导出
     */
    @SneakyThrows
    @Override
    public void exportByPage(HttpServletResponse response) {
        int count = userTestMapper.queryCount();
        String fileName = "test";
        long start = System.currentTimeMillis();
        ServletOutputStream outputStream = response.getOutputStream();
        WriteWorkbook writeWorkbook = new WriteWorkbook();
        writeWorkbook.setOutputStream(outputStream);
        writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
        ExcelWriter writer = EasyExcel.write(outputStream).build();
        // 每一个sheet的条数
        int sheetDataRows = PER_SHEET_ROW_COUNT;
        // 每次写入的条数
        int writeDataRows = PER_WRITE_ROW_COUNT;
        // 计算需要的sheet数量
        int sheetNum = count % sheetDataRows == 0 ? count / sheetDataRows : (count / sheetDataRows) + 1;
        // 计算一般情况下每一个sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
        int oneSheetWriteCount = sheetDataRows / writeDataRows;
        //计算最后一个sheet需要写入的次数
        int lastSheetWriteCount = count % sheetDataRows == 0 ? oneSheetWriteCount : (count % sheetDataRows % writeDataRows == 0 ? (count % sheetDataRows / writeDataRows) : (count % sheetDataRows / writeDataRows + 1));
        //开始分批查询分次写入
        for (int i = 0; i < sheetNum; i++) {
            // 循环写入次数
            for (int j = 0; j < (i == sheetNum - 1 ? lastSheetWriteCount : oneSheetWriteCount); j++) {
//                PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
                int startIndex = (j + oneSheetWriteCount * i) * writeDataRows;
                List<UserTest> userTestList = userTestMapper.selectListByPage(startIndex, writeDataRows);
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1))
                        .head(UserTest.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                writer.write(userTestList, writeSheet);
            }
        }
        // 下载EXCEL，返回给前段stream流
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        writer.finish();
        outputStream.flush();
        outputStream.close();
        System.out.println("分页查询导出结束：" + (System.currentTimeMillis() - start) + "ms");
    }
}
